from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey
import os
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from dotenv import load_dotenv
from contextlib import contextmanager
import json

load_dotenv()  # 默认加载当前目录下的.env文件
Base = declarative_base()


@contextmanager
def get_db_session():
    session = Session()
    try:
        yield session
        session.commit()
    except Exception as e:
        session.rollback()
        raise
    finally:
        session.close()


class Job(Base):
    __tablename__ = "jobs"
    job_name = Column(String(100), comment="职位名称")
    company = Column(String(100), comment="公司名称")
    city = Column(String(100), comment="所在城市")
    experience = Column(String(100), comment="工作经验要求")
    education = Column(String(100), comment="学历要求")
    description = Column(Text(), comment="职位描述")
    job_id = Column(String(100), primary_key=True, comment="职位id")
    results = relationship("Result", back_populates="job")

    def __repr__(self):
        return json.dumps(
            {
                "job_name": self.job_name,
                "job_id": self.job_id,
                "company": self.company,
                "city": self.city,
                "experience": self.experience,
                "education": self.education,
                "description": self.description,
            },
            ensure_ascii=False,
        )


class Result(Base):
    __tablename__ = "results"
    id = Column(Integer, primary_key=True, autoincrement=True)
    file_path = Column(String(100), comment="简历路径")
    analyze_result = Column(Text(), comment="AI分析结果")
    # job_id 是关联Jobs表的job_id
    job_id = Column(String(100), ForeignKey("jobs.job_id"))
    job = relationship("Job", back_populates="results")

    def __repr__(self):
        return json.dumps(
            {
                "file_path": self.file_path,
                "job_id": self.job_id,
                "id": self.id,
                "analyze_result": self.analyze_result,
            },
            ensure_ascii=False,
        )


MYSQL_HOST = os.getenv("MYSQL_HOST", "localhost")
MYSQL_USER = os.getenv("MYSQL_USER", "root")
MYSQL_PASSWORD = os.getenv("MYSQL_PASSWORD")
print("MYSQL_PASSWORD", MYSQL_PASSWORD)
MYSQL_PORT = os.getenv("MYSQL_PORT", "3306")
MYSQL_DB = os.getenv("MYSQL_DB", "jobs")
CHROME_PATH = os.getenv("CHROME_PATH")
SEARCH_KEY = os.getenv("SEARCH_KEY", "AI前端")
engine_url = f"mysql+pymysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DB}"
# 创建引擎
engine = create_engine(engine_url)
# 创建表
Base.metadata.create_all(engine)  #
Session = sessionmaker(bind=engine)
